package com.amkai.aes.webservice.mobile.objecthandler;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

import org.hibernate.Query;

import com.amkai.aes.aescommon.schedule.AppointmentHelper;
import com.amkai.aes.generatedbeans.common.Address;
import com.amkai.aes.generatedbeans.common.AddressKind;
import com.amkai.aes.generatedbeans.common.City;
import com.amkai.aes.generatedbeans.common.Country;
import com.amkai.aes.generatedbeans.common.County;
import com.amkai.aes.generatedbeans.common.Location;
import com.amkai.aes.generatedbeans.common.Phone;
import com.amkai.aes.generatedbeans.common.Zip;
import com.amkai.aes.generatedbeans.common.ZipCode;
import com.amkai.aes.generatedbeans.passport.Passport;
import com.amkai.aes.generatedbeans.physstaff.PhysStaff;
import com.amkai.aes.generatedbeans.pmcase.PmCase;
import com.amkai.aes.generatedbeans.procedure.Procedure;
import com.amkai.aes.generatedbeans.procedure.items.ProcedureType;
import com.amkai.aes.generatedbeans.reference.PhysStaffReference;
import com.amkai.aes.generatedbeans.reference.Reference;
import com.amkai.aes.generatedbeans.reference.ReferenceKind;
import com.amkai.aes.generatedbeans.registration.Account;
import com.amkai.aes.generatedbeans.registration.Patient;
import com.amkai.aes.generatedbeans.registration.Person;
import com.amkai.aes.generatedbeans.scheduling.AbstractAppointment;
import com.amkai.aes.generatedbeans.scheduling.AssistantUsage;
import com.amkai.aes.generatedbeans.scheduling.DxUsage;
import com.amkai.aes.generatedbeans.scheduling.PracticeManagementAppointment;
import com.amkai.aes.generatedbeans.scheduling.Reservation;
import com.amkai.aes.generatedbeans.scheduling.ResourceConnection;
import com.amkai.aes.generatedbeans.scheduling.ResourceConnectionProperty;
import com.amkai.aes.generatedbeans.scheduling.ResourceConnectionPropertyKind;
import com.amkai.aes.generatedbeans.scheduling.ResourceConnectionRole;
import com.amkai.aes.generatedbeans.scheduling.SchedulingResource;
import com.amkai.aes.generatedbeans.scheduling.SurgeonConnectionProperty;
import com.amkai.aes.generatedbeans.scheduling.SurgeryCenterAppointment;
import com.amkai.aes.generatedbeans.scheduling.SurgeryProcedureUsage;
import com.amkai.aes.generatedbeans.scheduling.resources.AppointmentType;
import com.amkai.aes.generatedbeans.scheduling.resources.AppointmentTypeKind;
import com.amkai.aes.webservice.mobile.MobileServerConfigManager;
import com.amkai.common.extensions.hibernate.config.manager.SessionManager;
import com.amkai.mobile.bean.common.MobileAddress;
import com.amkai.mobile.bean.dictionary.MobileAnest;
import com.amkai.mobile.bean.dictionary.MobileAssistant;
import com.amkai.mobile.bean.dictionary.MobileCRNA;
import com.amkai.mobile.bean.dictionary.MobileProcedure;
import com.amkai.mobile.bean.dictionary.MobileProvider;
import com.amkai.mobile.bean.dictionary.MobileSurgeon;
import com.amkai.mobile.bean.pmcase.MobileCase;
import com.amkai.mobile.bean.registration.MobileAccount;
import com.amkai.mobile.bean.registration.MobilePatient;
import com.amkai.mobile.bean.scheduler.appointment.MobileAppointmentBrief;
import com.amkai.mobile.bean.scheduler.appointment.MobileAppointmentDetails;
import com.amkai.mobile.bean.scheduler.appointment.MobileAppointmentType;
import com.amkai.mobile.bean.scheduler.appointment.MobileDX;
import com.amkai.mobile.bean.scheduler.appointment.MobileProcedureType;
import com.amkai.mobile.bean.scheduler.appointment.MobileSchedulerTarget;
import com.amkai.mobile.bean.scheduler.appointment.MobileSurgeryProcedureUsage;
import com.amkai.mobile.common.job.scheduler.MobileSchedulerJob;

/**
 * 
 * @author lkovari
 *
 */
public class MobileSchedulerServerManager  {
	private SessionManager sessionManager = null;
	private static final String DOBFORMAT = "MM/dd/yyyy";
	private static final String DATEFORMAT = "MM/dd/yy";
	private static final String TIMEFORMAT = "hh:mma";

	public MobileSchedulerServerManager(SessionManager sm) throws Exception {
		this.sessionManager = sm;
	}
	
	private String getTimeInString(Long time, int dateFormat) {
		Calendar calendar = Calendar.getInstance();
		calendar.setTimeInMillis(time);
		String timeInString = DateFormat.getTimeInstance(dateFormat).format(calendar.getTime());
		return timeInString;
	}

	private String getDateInString(Long time, int dateFormat) {
		Calendar calendar = Calendar.getInstance();
		calendar.setTimeInMillis(time);
		SimpleDateFormat dateTimeFormat = new SimpleDateFormat(DATEFORMAT);
		String dateInString = dateTimeFormat.format(calendar.getTime());		
		return dateInString;
	}
	
	/**
	 * 
	 * @param mobileSchedulerJob
	 * @return
	 * @throws Exception
	 */
	public boolean isSCorSHAppointment(MobileSchedulerJob mobileSchedulerJob) throws Exception {
		boolean isSCorSH = false;
		sessionManager.clear();
		String queryString = "SELECT aa FROM AbstractAppointment aa WHERE aa.id = :apptId";
		AbstractAppointment abstractAppointment = (AbstractAppointment) sessionManager.execute(queryString).setParameter("apptId", mobileSchedulerJob.getAppointmentId()).uniqueResult();
		if (abstractAppointment != null) {
			isSCorSH = abstractAppointment.getAppointmentKind().equals(AppointmentTypeKind.OR);
		}
		return isSCorSH;
	}

	/**
	 * 
	 * @param person
	 * @return
	 * @throws Exception 
	 */
	private Location extractAddress(Long addrId) throws Exception {
		String queryString = "SELECT loc FROM Location loc WHERE loc.id = :locId"; 
		Calendar c = Calendar.getInstance();
		Query query = sessionManager.execute(queryString);
		query.setParameter("locId", addrId);
		Location loc = (Location) query.uniqueResult();
		if (loc != null) {
			if (loc != null) {
				if (loc.getAlterPhone() != null) {
					Phone alterPhone = sessionManager.get(Phone.class, loc.getAlterPhone().getId());
					loc.setAlterPhone(alterPhone);
				}
				if (loc.getPhone() != null) {
					Phone phone = sessionManager.get(Phone.class, loc.getPhone().getId());
					loc.setPhone(phone);
				}
				ZipCode zip = loc.getZipCode();
				if (zip != null) {
					if (zip.getCity() != null) {
						City city = sessionManager.get(City.class, zip.getCity().getId());
						city.getName();
					}
					if (zip.getZip() != null) {
						Zip zipCode = sessionManager.get(Zip.class, zip.getZip().getId());
						zipCode.getName();
						if (zipCode.getCountry() != null) {
							Country country = sessionManager.get(Country.class, zipCode.getCountry().getId());
							country.getName();
						}
						if (zipCode.getCounty() != null) {
							County county = sessionManager.get(County.class, zipCode.getCounty().getId());
							county.getName();
						}
					}
				}
			}
		}
		return loc;
	}
	
	/**
	 * 
	 * @param aa
	 * @return
	 * @throws Exception
	 */
	private Person extractPerson(AbstractAppointment aa) throws Exception {
		Person person = sessionManager.get(Person.class, aa.getPerson().getId());
		return person;
	}

	/**
	 * 
	 * @param aa
	 * @return
	 * @throws Exception
	 */
	private PmCase extractPmCase(AbstractAppointment aa) throws Exception {
		PmCase pmCase = null;
		if (aa.getPmCase() != null)
			pmCase = sessionManager.get(PmCase.class, aa.getPmCase().getId());
		return pmCase;
	}
	
	/**
	 * 
	 * @param aa
	 * @return
	 * @throws Exception
	 */
	private Patient extractPatient(AbstractAppointment aa) throws Exception {
		Patient patient = null;
		PmCase pmCase = extractPmCase(aa);
		if (pmCase != null) {
			patient = sessionManager.get(Patient.class, pmCase.getPatient().getId());
		}
		return patient;
	}
	
	/**
	 * 
	 * @param aa
	 * @return
	 * @throws Exception
	 */
	private Account extractAccount(AbstractAppointment aa) throws Exception {
		Account account = null;
		Patient patient = extractPatient(aa);
		if (patient != null) {
			account = sessionManager.get(Account.class, patient.getAccount().getId());
		}
		return account;
	}

	private Reservation extractReservationByRC(ResourceConnection rc) throws Exception {
		Reservation res = sessionManager.get(Reservation.class, rc.getReservation().getId());
		return res;
	}
	
	/**
	 * 
	 * @param aa
	 * @return
	 * @throws Exception
	 */
	private AppointmentType extractAppointmentType(AbstractAppointment aa) throws Exception {
		AppointmentType appointmentType = (AppointmentType)sessionManager.get(AppointmentType.class, aa.getAppointmentType().getId());
		return appointmentType;
	}


	/**
	 * 
	 * @param aa
	 * @return
	 * @throws Exception
	 */
	private SchedulingResource extractSR(AbstractAppointment aa, ResourceConnectionRole role) throws Exception {
		SchedulingResource foundSR = null;
		for (ResourceConnection rc : aa.getResourceConnections()) {
			rc.getRole();
			if (rc.getRole().equals(role)) {
				Reservation res = extractReservationByRC(rc);
				if (res != null) {
					SchedulingResource sr = sessionManager.get(SchedulingResource.class, res.getSchedulingResource().getId());
					if (sr != null) {
						sr.getTitle();
						sr.getId();
						sr.getActive();
						sr.getShortName();
						sr.getKind();
						foundSR = sr;
						break;
					}
				}
			}
		}
		return foundSR;
	}
	

	/**
	 * 
	 * @param person
	 * @return
	 * @throws Exception
	 */
	private String extractGender(Person person) throws Exception {
		String gender = (person.getIsMale()) ? "M" : "F";
		return gender;
	}
	
	/**
	 * 
	 * @param aa
	 * @return
	 * @throws Exception 
	 */
	private MobileAppointmentBrief captureAppointmentBriefData(AbstractAppointment aa) throws Exception {
		MobileAppointmentBrief mobileAppointment = new MobileAppointmentBrief();
		// time range
		mobileAppointment.date = getDateInString(aa.getBeginTime(), DateFormat.MEDIUM);
		mobileAppointment.beginTime = getTimeInString(aa.getBeginTime(), DateFormat.SHORT);
		mobileAppointment.endTime = getTimeInString(aa.getEndTime(), DateFormat.SHORT);
		mobileAppointment.id = aa.getId();
		if (aa.getAppointmentKind().equals(AppointmentTypeKind.OR)) {
			// room
			SchedulingResource roomSR = extractSR(aa, ResourceConnectionRole.TARGET);
			if (roomSR != null) {
				mobileAppointment.appointmentTitle = " " + roomSR.getTitle() + " "; 
			}
			// Appointment type
			AppointmentType appointmentType = extractAppointmentType(aa);
			if (appointmentType != null) {
				mobileAppointment.appointmentBody = appointmentType.getName();
				mobileAppointment.appointmentColor = appointmentType.getColorRGB();
			}
			// patient gender
			Person person = extractPerson(aa);
			if (person != null) {
				String gender = extractGender(person);
				mobileAppointment.appointmentBody += person.getLastName().charAt(0) + " " + person.getFirstName() + " (" + gender + ") ";
			}
			// account#
			Account account = extractAccount(aa);
			if (account != null) {
				mobileAppointment.appointmentBody += "#" + account.getAccountNumber();
				mobileAppointment.isPatient = true;
			}
			else {
				mobileAppointment.isPatient = false;
			}
		}
		else if (aa.getAppointmentKind().equals(AppointmentTypeKind.PM)) {
			// Appointment type
			AppointmentType appointmentType = extractAppointmentType(aa);
			if (appointmentType != null) {
				mobileAppointment.appointmentTitle = appointmentType.getName();
				mobileAppointment.appointmentColor = appointmentType.getColorRGB();
			}
			// patient name account#
			Person person = extractPerson(aa);
			if (person != null) {
				String gender = extractGender(person);
				mobileAppointment.appointmentBody = person.getLastName().charAt(0) + " " + person.getFirstName() + " (" + gender + ") ";
			}
			Account account = extractAccount(aa);
			if (account != null) {
				mobileAppointment.appointmentBody += "#" + account.getAccountNumber();
				mobileAppointment.isPatient = true;
			}
			else {
				mobileAppointment.isPatient = false;
			}
		}
		return mobileAppointment;
	}

	/**
	 * 
	 * @param rcpo
	 * @return
	 */
	public SurgeonConnectionProperty extractSurgeonConnectionProperty(Object[] rcpo) {
		SurgeonConnectionProperty surgeonConnectionProperty = null;
		ResourceConnectionProperty rcp = null; 
		if ((rcpo != null) && (rcpo.length > 0)) {
			rcp = (ResourceConnectionProperty)rcpo[0];
			if (rcp.getPropertyKind().equals(ResourceConnectionPropertyKind.RCP_SCP)) {
				surgeonConnectionProperty = (SurgeonConnectionProperty)rcp;
			}	
		}		
		return surgeonConnectionProperty;
	}

	/**
	 * 
	 * @param aa
	 * @param role
	 * @return
	 * @throws Exception
	 */
	public List<SchedulingResource> extractSchedulingResourcesByRole(AbstractAppointment aa, ResourceConnectionRole role) throws Exception {
		List<SchedulingResource> schedulingResources = new ArrayList<SchedulingResource>(); 
		List<ResourceConnection> resourceConnections =  new ArrayList<ResourceConnection>();
		for(ResourceConnection rc : aa.getResourceConnections()) {
			if(rc.getRole() == role) {
				resourceConnections.add(rc);
			}	
		}
		if (resourceConnections.size() >= 1) {
			List<SurgeonConnectionProperty> scps = new ArrayList<SurgeonConnectionProperty>();
			for(ResourceConnection rc : resourceConnections) {
				Object[] rcpo = rc.getResourceConnectionProperty().toArray();
				SurgeonConnectionProperty scp = extractSurgeonConnectionProperty(rcpo);
				if (scp != null) {
					scps.add(scp);
				}
			}
			Collections.sort(scps, new Comparator<SurgeonConnectionProperty>(){
				@Override
				public int compare(SurgeonConnectionProperty o1, SurgeonConnectionProperty o2) {
					return o1.getSeq().compareTo(o2.getSeq());
				}
			});
			for (SurgeonConnectionProperty scp : scps) {
				ResourceConnection resConn = sessionManager.get(ResourceConnection.class, scp.getResourceConnection().getId());
				Reservation res = sessionManager.get(Reservation.class, resConn.getReservation().getId());
				SchedulingResource sr = sessionManager.get(SchedulingResource.class, res.getSchedulingResource().getId());
				schedulingResources.add(sr);
			}
			scps.clear();
			scps = null;
		}
		resourceConnections.clear();
		resourceConnections = null;
		return schedulingResources;
	}
	
	/**
	 * 
	 * @param value
	 * @param cnt
	 * @return
	 */
	private String fillWith(String value, int cnt) {
		String text = "";
		for (int ix = 0; ix < cnt; ix++)
			text += value;
		return text;
	}
	
	/**
	 * 
	 * @param aa
	 * @return
	 * @throws Exception
	 */
	private MobilePatient capturePatientDetailsData(AbstractAppointment aa) throws Exception {
		MobilePatient mobilePatient = null;
		// patient name account#
		Person person = extractPerson(aa);
		if (person != null) {
			String gender = extractGender(person);
			mobilePatient = new MobilePatient();
			mobilePatient.gender = gender;
			Patient patient = extractPatient(aa);
			if (patient != null) {
				mobilePatient.id = patient.getId(); 
			}
			SimpleDateFormat sdf = new SimpleDateFormat(DOBFORMAT);
			mobilePatient.dob = sdf.format(person.getDateOfBirth());;
			sdf = null;
			if (MobileServerConfigManager.IsShowPatientName() == null) {
				mobilePatient.lastName = person.getLastName();
				mobilePatient.firstName = person.getFirstName();
			}
			else if (MobileServerConfigManager.IsShowPatientName()) {
				mobilePatient.lastName = person.getLastName();
				mobilePatient.firstName = person.getFirstName();
			}
			else {
				mobilePatient.lastName = fillWith("#", person.getLastName().length());
				mobilePatient.firstName = fillWith("#", person.getFirstName().length());
			}
			Calendar dob = Calendar.getInstance();
			dob.setTime(aa.getPerson().getDateOfBirth());
			mobilePatient.age = Calendar.getInstance().get(Calendar.YEAR) - dob.get(Calendar.YEAR);
			mobilePatient.ssn = person.getSsn();
			//Address
			if (person.getHomeAddress() != null) {
				Location homeLocation = sessionManager.get(Location.class, person.getHomeAddress().getId());
				Location location = extractAddress(person.getHomeAddress().getId());
				if (location != null) {
					MobileAddress mobileAddress = new MobileAddress();
					mobileAddress.id = location.getId();
					if (location != null) {
						mobileAddress.address1 = location.getLine1(); 
						mobileAddress.address2 = location.getLine2();
						if (location.getZipCode() != null) {
							ZipCode zip = location.getZipCode();
							if (zip.getCity() != null) {
								mobileAddress.city = zip.getCity().getName();
								if (zip.getZip() != null) {
									mobileAddress.zip = zip.getZip().getName();
									if (zip.getZip().getCountry() != null) {
										mobileAddress.country = zip.getZip().getCountry().getName();
										mobileAddress.county = zip.getZip().getCountry().getName();
									}
								}
							}
						}
 					}
					mobilePatient.homeAddress = mobileAddress;
				}
			}
			
			if (person.getWorkAddress() != null) {
				Location location = extractAddress(person.getWorkAddress().getId());
				if (location != null) {
					MobileAddress mobileAddress = new MobileAddress();
					mobileAddress.id = location.getId();
					if (location != null) {
						mobileAddress.address1 = location.getLine1(); 
						mobileAddress.address2 = location.getLine2();
						if (location.getZipCode() != null) {
							ZipCode zip = location.getZipCode();
							if (zip.getCity() != null) {
								mobileAddress.city = zip.getCity().getName();
								if (zip.getZip() != null) {
									mobileAddress.zip = zip.getZip().getName();
									if (zip.getZip().getCountry() != null) {
										mobileAddress.country = zip.getZip().getCountry().getName();
										mobileAddress.county = zip.getZip().getCountry().getName();
									}
								}
							}
						}
 					}
					mobilePatient.workAddress = mobileAddress;
				}
			}
			// Account
		}
		Account account = extractAccount(aa);
		if (account != null) {
			mobilePatient.mobileAccount = new MobileAccount();
			mobilePatient.mobileAccount.id = account.getId();
			mobilePatient.mobileAccount.number = account.getAccountNumber();
		}
		return mobilePatient;
	}

	/**
	 * 
	 * @param aa
	 * @return
	 * @throws Exception
	 */
	private MobileCase captureCaseDetailsData(AbstractAppointment aa) throws Exception {
		MobileCase mobileCase = null;
		// PmCase
		PmCase pmCase = extractPmCase(aa);
		if (pmCase != null) {
			mobileCase = new MobileCase();
			mobileCase.id = pmCase.getId();
			mobileCase.name = pmCase.getName();
			mobileCase.status = pmCase.getStatus().name();
		}
		return mobileCase;
	}
	
	/**
	 * 
	 * @param aa
	 * @return
	 * @throws Exception
	 */
	private MobileAppointmentDetails captureAppointmentDetailsData(AbstractAppointment aa) throws Exception {
		MobileAppointmentDetails mobileAppointment = new MobileAppointmentDetails();
		// time range
		mobileAppointment.beginTime = getTimeInString(aa.getBeginTime(), DateFormat.SHORT);
		mobileAppointment.endTime = getTimeInString(aa.getEndTime(), DateFormat.SHORT);
		mobileAppointment.status = aa.getStatus().name();
		if (aa.getAppointmentKind().equals(AppointmentTypeKind.OR)) {
			mobileAppointment.isSCorSH = true;
			if (aa instanceof SurgeryCenterAppointment) {
				SurgeryCenterAppointment sca = (SurgeryCenterAppointment)aa;
				mobileAppointment.cleanup = sca.getCleanup();
			}

			// surgeon
			SchedulingResource surgSR = extractSR(aa, ResourceConnectionRole.SURGEON);
			if (surgSR != null) {
				mobileAppointment.mobileSurgeon = new MobileSurgeon();
				mobileAppointment.mobileSurgeon.id = surgSR.getId();
				mobileAppointment.mobileSurgeon.name = surgSR.getTitle();
				mobileAppointment.mobileSurgeon.shortName = surgSR.getShortName();
			}

			// anest
			SchedulingResource anestSR = extractSR(aa, ResourceConnectionRole.ANEST);
			if (anestSR != null) {
				mobileAppointment.mobileAnest = new MobileAnest();
				mobileAppointment.mobileAnest.id = anestSR.getId();
				mobileAppointment.mobileAnest.name = anestSR.getTitle();
				mobileAppointment.mobileAnest.shortName = anestSR.getShortName();
			}

			// CRNA
			SchedulingResource crnaSR = extractSR(aa, ResourceConnectionRole.CRNA);
			if (crnaSR != null) {
				mobileAppointment.mobileCRNA = new MobileCRNA();
				mobileAppointment.mobileCRNA.id = crnaSR.getId();
				mobileAppointment.mobileCRNA.name = crnaSR.getTitle();
				mobileAppointment.mobileCRNA.shortName = crnaSR.getShortName();
			}
			
			// room
			SchedulingResource roomSR = extractSR(aa, ResourceConnectionRole.TARGET);
			if (roomSR != null) {
				mobileAppointment.mobileSchedulerTarget = new MobileSchedulerTarget();
				mobileAppointment.mobileSchedulerTarget.name = roomSR.getTitle();
				mobileAppointment.mobileSchedulerTarget.shortName = roomSR.getShortName();
				mobileAppointment.mobileSchedulerTarget.id = roomSR.getId();
			}
			// procedure
			List<MobileSurgeryProcedureUsage> mobileSpus = new ArrayList<MobileSurgeryProcedureUsage>();
			List<SurgeryProcedureUsage> spus = AppointmentHelper.extractAppointmentSurgeryProcedureUsages(aa);
			if (spus != null) {
				for (SurgeryProcedureUsage spu: spus) {
					MobileSurgeryProcedureUsage mspu = new MobileSurgeryProcedureUsage();
					mspu.seq = spu.getSequence();
					// procedure
					mspu.mobileProcedure =  new MobileProcedure();
					mspu.mobileProcedure.name = spu.getProcedure().getName();
					mspu.mobileProcedure.shortName = spu.getProcedure().getShortName();
					// procedure type
					if (spu.getProcedure().getProcedureType() != null) {
						ProcedureType procType = sessionManager.get(ProcedureType.class, spu.getProcedure().getProcedureType().getId());
						mspu.mobileProcedureType = new MobileProcedureType();
						mspu.mobileProcedureType.id = procType.getId();
						mspu.mobileProcedureType.name = procType.getName();
						mspu.mobileProcedureType.shortName = procType.getShortName();
					}
					if (spu.getBodySide() != null)
						mspu.lrb = spu.getBodySide().name();
					mspu.prv = "";
					Set<AssistantUsage> assistantUsages =  spu.getAssistantUsages();
					if ((assistantUsages != null) && (assistantUsages.size() > 0)) {
						List<AssistantUsage> assistants = new ArrayList<AssistantUsage>(assistantUsages);
						AssistantUsage asstntUsage = assistants.get(0);
						if (asstntUsage != null) {
							mspu.mobileAssistant1 = new MobileAssistant();
							ResourceConnection rc = sessionManager.get(ResourceConnection.class, asstntUsage.getResourceConnection().getId());
							Reservation res = sessionManager.get(Reservation.class, rc.getReservation().getId());
							SchedulingResource sr = sessionManager.get(SchedulingResource.class, res.getSchedulingResource().getId());
							mspu.mobileAssistant1.id = sr.getId();  
							mspu.mobileAssistant1.name = sr.getTitle();  
							mspu.mobileAssistant1.shortName = sr.getShortName();  
						}
						if (assistants.size() > 1) {
							asstntUsage = assistants.get(1);
							if (asstntUsage != null) {
								ResourceConnection rc = sessionManager.get(ResourceConnection.class, asstntUsage.getResourceConnection().getId());
								Reservation res = sessionManager.get(Reservation.class, rc.getReservation().getId());
								SchedulingResource sr = sessionManager.get(SchedulingResource.class, res.getSchedulingResource().getId());
								mspu.mobileAssistant2 = new MobileAssistant();
								mspu.mobileAssistant2.id = sr.getId();  
								mspu.mobileAssistant2.name = sr.getTitle();  
								mspu.mobileAssistant2.shortName = sr.getShortName();  
							}
						}
					}
					if ((spu.getDxUsages() != null) && (spu.getDxUsages().size() > 0)) {
						List<DxUsage> dxUsages = new ArrayList<DxUsage>(spu.getDxUsages());
						Collections.sort(dxUsages, new Comparator<DxUsage>() {
							@Override
							public int compare(DxUsage o1, DxUsage o2) {
								return o1.getSeq().compareTo(o2.getSeq());
							}
						});
						mspu.dx1 = new MobileDX();
						for (int ix = 0; ix < dxUsages.size(); ix++) {
							DxUsage dxu = dxUsages.get(ix);
							switch (ix) {
							case 0: {
								mspu.dx1 = new MobileDX();
								mspu.dx1.id = dxu.getId();
								mspu.dx1.name = dxu.getDiagnosis().getName();
								mspu.dx1.shortName = dxu.getDiagnosis().getShortName();
								break;
							}
							case 1: {	
								mspu.dx2 = new MobileDX();
								mspu.dx2.id = dxu.getId();
								mspu.dx2.name = dxu.getDiagnosis().getName();
								mspu.dx2.shortName = dxu.getDiagnosis().getShortName();
								break;
							}
							case 2: {	
								mspu.dx3 = new MobileDX();
								mspu.dx3.id = dxu.getId();
								mspu.dx3.name = dxu.getDiagnosis().getName();
								mspu.dx3.shortName = dxu.getDiagnosis().getShortName();
								break;
							}
							case 3: {	
								mspu.dx4 = new MobileDX();
								mspu.dx4.id = dxu.getId();
								mspu.dx4.name = dxu.getDiagnosis().getName();
								mspu.dx4.shortName = dxu.getDiagnosis().getShortName();
								break;
							}
							}
						}
					}
					mobileSpus.add(mspu);
				}
				// Surgeon
				List<SchedulingResource> surgeons = extractSchedulingResourcesByRole(aa, ResourceConnectionRole.SURGEON);
				int ix = 0;
				for (MobileSurgeryProcedureUsage mspu : mobileSpus) {
					SchedulingResource sr = surgeons.get(ix);
					mspu.mobileSurgeon = new MobileSurgeon();
					mspu.mobileSurgeon.id = sr.getId();
					mspu.mobileSurgeon.name = sr.getTitle();
					mspu.mobileSurgeon.shortName = sr.getShortName();
					ix++;
				}
			}
			mobileAppointment.mobileSurgeryProcedureUsages = mobileSpus;
		}
		else {
			mobileAppointment.isSCorSH = false;
			PracticeManagementAppointment pma = (PracticeManagementAppointment)aa;
			// room
			SchedulingResource provSR = extractSR(aa, ResourceConnectionRole.TARGET);
			if (provSR != null) {
				mobileAppointment.mobileSchedulerTarget = new MobileSchedulerTarget();
				mobileAppointment.mobileSchedulerTarget.name = provSR.getTitle();
				mobileAppointment.mobileSchedulerTarget.shortName = provSR.getShortName();
				mobileAppointment.mobileSchedulerTarget.id = provSR.getId();
			}
			//provider
			SchedulingResource referringSR = extractSR(aa, ResourceConnectionRole.REFERRING_PROVIDER);
			if (referringSR != null) {
				mobileAppointment.referring = new MobileProvider();
				mobileAppointment.referring.name = referringSR.getTitle();
				mobileAppointment.referring.shortName = referringSR.getShortName();
				mobileAppointment.referring.id = referringSR.getId();
			}
		}
		// patient name account#
//		Person person = extractPerson(aa);
//		if (person != null) {
//			String gender = extractGender(person);
//			mobileAppointment.mobilePatient = new MobilePatient();
//			mobileAppointment.mobilePatient.gender = gender;
//			Patient patient = extractPatient(aa);
//			if (patient != null) {
//				mobileAppointment.mobilePatient.id = patient.getId(); 
//			}
//			SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
//			mobileAppointment.mobilePatient.dob = sdf.format(person.getDateOfBirth());;
//			sdf = null;
//			mobileAppointment.mobilePatient.lastName = person.getLastName();
//			mobileAppointment.mobilePatient.firstName = person.getFirstName();
//			Calendar dob = Calendar.getInstance();
//			dob.setTime(aa.getPerson().getDateOfBirth());
//			mobileAppointment.mobilePatient.age = Calendar.getInstance().get(Calendar.YEAR) - dob.get(Calendar.YEAR);
//			mobileAppointment.mobilePatient.ssn = person.getSsn();
//			//Address
//			if (person.getHomeAddress() != null) {
//				Location homeLocation = sessionManager.get(Location.class, person.getHomeAddress().getId());
//				Location location = extractAddress(person.getHomeAddress().getId());
//				if (location != null) {
//					MobileAddress mobileAddress = new MobileAddress();
//					mobileAddress.id = location.getId();
//					if (location != null) {
//						mobileAddress.address1 = location.getLine1(); 
//						mobileAddress.address2 = location.getLine2();
//						if (location.getZipCode() != null) {
//							ZipCode zip = location.getZipCode();
//							if (zip.getCity() != null) {
//								mobileAddress.city = zip.getCity().getName();
//								if (zip.getZip() != null) {
//									mobileAddress.zip = zip.getZip().getName();
//									if (zip.getZip().getCountry() != null) {
//										mobileAddress.country = zip.getZip().getCountry().getName();
//										mobileAddress.county = zip.getZip().getCountry().getName();
//									}
//								}
//							}
//						}
// 					}
//					mobileAppointment.mobilePatient.homeAddress = mobileAddress;
//				}
//			}
//			
//			if (person.getWorkAddress() != null) {
//				Location location = extractAddress(person.getWorkAddress().getId());
//				if (location != null) {
//					MobileAddress mobileAddress = new MobileAddress();
//					mobileAddress.id = location.getId();
//					if (location != null) {
//						mobileAddress.address1 = location.getLine1(); 
//						mobileAddress.address2 = location.getLine2();
//						if (location.getZipCode() != null) {
//							ZipCode zip = location.getZipCode();
//							if (zip.getCity() != null) {
//								mobileAddress.city = zip.getCity().getName();
//								if (zip.getZip() != null) {
//									mobileAddress.zip = zip.getZip().getName();
//									if (zip.getZip().getCountry() != null) {
//										mobileAddress.country = zip.getZip().getCountry().getName();
//										mobileAddress.county = zip.getZip().getCountry().getName();
//									}
//								}
//							}
//						}
// 					}
//					mobileAppointment.mobilePatient.workAddress = mobileAddress;
//				}
//			}
//			
//		}
		// Appointment type
		AppointmentType appointmentType = extractAppointmentType(aa);
		if (appointmentType != null) {
			mobileAppointment.mobileAppointmentType = new MobileAppointmentType();
			mobileAppointment.mobileAppointmentType.id = appointmentType.getId();
			mobileAppointment.mobileAppointmentType.name = appointmentType.getName();
			mobileAppointment.mobileAppointmentType.shortName = appointmentType.getShortName();
			mobileAppointment.mobileAppointmentType.color = appointmentType.getColorRGB();
		}
		
		// Account
//		Account account = extractAccount(aa);
//		if (account != null) {
//			mobileAppointment.mobileAccount = new MobileAccount();
//			mobileAppointment.mobileAccount.id = account.getId();
//			mobileAppointment.mobileAccount.number = account.getAccountNumber();
//		}
//		// PmCase
//		PmCase pmCase = extractPmCase(aa);
//		if (pmCase != null) {
//			mobileAppointment.mobileCase = new MobileCase();
//			mobileAppointment.mobileCase.id = pmCase.getId();
//			mobileAppointment.mobileCase.name = pmCase.getName();
//			mobileAppointment.mobileCase.status = pmCase.getStatus().name();
//		}
		return mobileAppointment;
	}
	
	/**
	 * 
	 * @param mobileSchedulerJob
	 * @return
	 * @throws Exception
	 */
	private List<AbstractAppointment> downloadAppointmentForBrief(MobileSchedulerJob mobileSchedulerJob) throws Exception {
		String queryString = "SELECT aa FROM AbstractAppointment aa "; 
//		queryString += "WHERE aa.bt >= :bt AND aa.et < :et AND aa.department.id = :depId ORDER BY aa.bt";	
		queryString += "WHERE aa.bt >= :bt AND aa.et < :et AND aa.department.id IN (:ids) ORDER BY aa.bt";
		Calendar c = Calendar.getInstance();
		c.set(Calendar.YEAR, mobileSchedulerJob.getAppointmentYear());
		c.set(Calendar.DAY_OF_YEAR, mobileSchedulerJob.getAppointmentDayOfTheYear());
		c.set(Calendar.HOUR_OF_DAY, 0);
		c.set(Calendar.HOUR, 0);
		c.set(Calendar.AM_PM, Calendar.AM);
		c.set(Calendar.MINUTE, 0);
		long timeBegin = c.getTimeInMillis();
		c.set(Calendar.HOUR_OF_DAY, 23);
		c.set(Calendar.HOUR, 11);
		c.set(Calendar.AM_PM, Calendar.PM);
		c.set(Calendar.MINUTE, 59);
		long timeEnd = c.getTimeInMillis();
		Query query = sessionManager.execute(queryString);
		query.setParameter("bt", timeBegin);
		query.setParameter("et", timeEnd);
//		query.setParameter("depId", mobileSchedulerJob.getDepartmentId());
		query.setParameterList("ids", mobileSchedulerJob.getDepIds());		
		List<AbstractAppointment> abstractAppointments = query.list();
		return abstractAppointments;
	}
	
	private List<AbstractAppointment> downloadAppointmentForNearestBrief(MobileSchedulerJob mobileSchedulerJob) throws Exception {
		String queryString = "SELECT aa FROM AbstractAppointment aa "; 
//		queryString += "WHERE aa.bt >= :bt AND aa.et < :et AND aa.department.id = :depId ORDER BY aa.bt";	
		queryString += "WHERE aa.bt >= :bt AND aa.et < :et ORDER BY aa.bt";		
		Calendar c = Calendar.getInstance();
		c.set(Calendar.YEAR, mobileSchedulerJob.getAppointmentYear());
		c.set(Calendar.DAY_OF_YEAR, mobileSchedulerJob.getAppointmentDayOfTheYear());
		c.set(Calendar.HOUR_OF_DAY, 0);
		c.set(Calendar.HOUR, 0);
		c.set(Calendar.AM_PM, Calendar.AM);
		c.set(Calendar.MINUTE, 0);
		long timeBegin = c.getTimeInMillis();
		c.set(Calendar.HOUR_OF_DAY, 23);
		c.set(Calendar.HOUR, 11);
		c.set(Calendar.AM_PM, Calendar.PM);
		c.set(Calendar.MINUTE, 59);
		long timeEnd = c.getTimeInMillis();
		Query query = sessionManager.execute(queryString);
		query.setParameter("bt", timeBegin);
		query.setParameter("et", timeEnd);
//		query.setParameter("depId", mobileSchedulerJob.getDepartmentId());
		List<AbstractAppointment> abstractAppointments = query.list();
		List<AbstractAppointment> abstractAppointmentsByPhys = new ArrayList<AbstractAppointment>();
		Map<Long,MobileAppointmentBrief> validationList = new TreeMap<Long, MobileAppointmentBrief>();
		for(AbstractAppointment aa: abstractAppointments) {
			for (ResourceConnection rc: aa.getResourceConnections()) {
				Reservation res = rc.getReservation();
				SchedulingResource sr = res.getSchedulingResource();
				Reference ref = sessionManager.get(Reference.class, sr.getReference().getId());
				if (ref.getKind().equals(ReferenceKind.PHYSSTAFF)) {
					PhysStaffReference psRef = sessionManager.get(PhysStaffReference.class, ref.getId());
					PhysStaff physStaff = psRef.getPhysStaff();
					List<Long> physStaffIds = getPhysStaffFromPassport(sessionManager, mobileSchedulerJob.getPhysstaffId());
					for (Long physStaffId : physStaffIds) {
						if (physStaff.getId().equals(physStaffId)) {
							abstractAppointmentsByPhys.add(aa);
						}
					}
				}
			}
		}	
		return abstractAppointmentsByPhys;
	}
	
	/**
	 * 
	 * @param mobileSchedulerJob
	 * @return
	 * @throws Exception
	 */
	public List<MobileAppointmentBrief> downloadAppointmentsBrief(MobileSchedulerJob mobileSchedulerJob) throws Exception {
/*
 		SELECT * FROM scheduling_abstractappointment aa 
		JOIN scheduling_abstractscheduleissue si on si.timerange_id = aa.abstractscheduleissue_id 
		JOIN scheduling_resourceconnection rc on rc.scheduleissue_id = aa.abstractscheduleissue_id 
		JOIN scheduling_reservation res on res.timerange_id = rc.reservation_id 
		JOIN scheduling_schedulingresource sr on sr.id = res.resource_id 
		JOIN ref_physstaffreference phr on phr.reference_id = sr.reference_id 
		WHERE (aa.bt >= 1312862400000 AND aa.et < 1312948799999) AND phr.physstaff_id = 109379 AND si.department_id = 3	
 */
		
		List<MobileAppointmentBrief> apptResult = null;
//		String queryString = "SELECT * FROM scheduling_abstractappointment aa ";
//		queryString += "JOIN scheduling_abstractscheduleissue si on si.timerange_id = aa.abstractscheduleissue_id ";
//		queryString += "JOIN scheduling_resourceconnection rc on rc.scheduleissue_id = aa.abstractscheduleissue_id ";
//		queryString += "JOIN scheduling_reservation res on res.timerange_id = rc.reservation_id ";
//		queryString += "JOIN scheduling_schedulingresource sr on sr.id = res.resource_id ";
//		queryString += "JOIN ref_physstaffreference phr on phr.reference_id = sr.reference_id ";
////		queryString += "WHERE (aa.bt >= :bt AND aa.et < :et) AND rc.resourceconnectionrole = 7 AND phr.physstaff_id = :psId";
//		queryString += "WHERE (aa.bt >= :bt AND aa.et < :et) AND phr.physstaff_id = :psId AND si.department_id = :depId";		

		List<AbstractAppointment> abstractAppointments = downloadAppointmentForBrief(mobileSchedulerJob);
		Map<Long,MobileAppointmentBrief> validationList = new TreeMap<Long, MobileAppointmentBrief>();
		for(AbstractAppointment aa: abstractAppointments) {
			for (ResourceConnection rc: aa.getResourceConnections()) {
				Reservation res = rc.getReservation();
				SchedulingResource sr = res.getSchedulingResource();
				Reference ref = sessionManager.get(Reference.class, sr.getReference().getId());
				if (ref.getKind().equals(ReferenceKind.PHYSSTAFF)) {
					PhysStaffReference psRef = sessionManager.get(PhysStaffReference.class, ref.getId());
					PhysStaff physStaff = psRef.getPhysStaff();
					List<Long> physStaffIds = getPhysStaffFromPassport(sessionManager, mobileSchedulerJob.getPhysstaffId());
					for (Long physStaffId : physStaffIds) {
						if (physStaff.getId().equals(physStaffId)) {
							MobileAppointmentBrief mobileAppointmentBrief = captureAppointmentBriefData(aa);
							// OR only
							if (aa.getAppointmentKind().equals(AppointmentTypeKind.OR)) {
								if (mobileAppointmentBrief.providerAs == null)
									mobileAppointmentBrief.providerAs = rc.getRole().toString();
								else
									mobileAppointmentBrief.providerAs += " " + rc.getRole().toString();
							}
							else {
								mobileAppointmentBrief.providerAs = " provider";
							}
							if (apptResult == null)
								apptResult = new ArrayList<MobileAppointmentBrief>();
							if (!validationList.containsKey(aa.getId())) {
								apptResult.add(mobileAppointmentBrief);
								validationList.put(new Long(aa.getId()), mobileAppointmentBrief);
							}	
						}
					}
				}
			}
		}
		validationList.clear();
		validationList = null;
		return apptResult;
	}

	private List<Long> getPhysStaffFromPassport(SessionManager sessionManager, long physstaffId) throws Exception {
		// TODO Auto-generated method stub
		List<Long> result;
		Passport passport = sessionManager.get(Passport.class, physstaffId);
		Long personId = passport.getPerson().getId();
		result = sessionManager.execute("SELECT ph.id FROM PhysStaff ph WHERE ph.person = :personid").setLong("personid", personId).list();
		return result;
	}
	/**
	 * 
	 * @param mobileSchedulerJob
	 * @return
	 * @throws Exception
	 */
	public MobileAppointmentDetails downloadAppointmentDetailsById(MobileSchedulerJob mobileSchedulerJob) throws Exception {
		MobileAppointmentDetails mobileAppointmentDetails = null;
		sessionManager.clear();
		String queryString = "SELECT aa FROM AbstractAppointment aa WHERE aa.id = :apptId";
		AbstractAppointment abstractAppointment = (AbstractAppointment) sessionManager.execute(queryString).setParameter("apptId", mobileSchedulerJob.getAppointmentId()).uniqueResult();
		if (abstractAppointment != null) {
			mobileAppointmentDetails = captureAppointmentDetailsData(abstractAppointment);
		}
		return mobileAppointmentDetails;
	}

	/**
	 * 
	 * @param mobileSchedulerJob
	 * @return
	 * @throws Exception
	 */
	public MobilePatient downloadPatientDetailsById(MobileSchedulerJob mobileSchedulerJob) throws Exception {
		MobilePatient mobilePatient = null;
		sessionManager.clear();
		String queryString = "SELECT aa FROM AbstractAppointment aa WHERE aa.id = :apptId";
		AbstractAppointment abstractAppointment = (AbstractAppointment) sessionManager.execute(queryString).setParameter("apptId", mobileSchedulerJob.getAppointmentId()).uniqueResult();
		if (abstractAppointment != null) {
			mobilePatient = capturePatientDetailsData(abstractAppointment);
		}
		return mobilePatient;
	}

	/**
	 * 
	 * @param mobileSchedulerJob
	 * @return
	 * @throws Exception
	 */
	public MobileCase downloadCaseDetailsById(MobileSchedulerJob mobileSchedulerJob) throws Exception {
		MobileCase mobileCase = null;
		sessionManager.clear();
		String queryString = "SELECT aa FROM AbstractAppointment aa WHERE aa.id = :apptId";
		AbstractAppointment abstractAppointment = (AbstractAppointment) sessionManager.execute(queryString).setParameter("apptId", mobileSchedulerJob.getAppointmentId()).uniqueResult();
		if (abstractAppointment != null) {
			mobileCase = captureCaseDetailsData(abstractAppointment);
		}
		return mobileCase;
	}

	private Calendar resetSecAndMils(Calendar time) {
		time.set(Calendar.SECOND, 0);
		time.set(Calendar.MILLISECOND, 0);
		return time;
	}
	
	/**
	 * 
	 * @return
	 */
	public MobileAppointmentBrief downloadNearestAppointment(MobileSchedulerJob mobileSchedulerJob) throws Exception {
		MobileAppointmentBrief mobileAppointmentBrief = null;
		AbstractAppointment appointmentBefore = null;
		AbstractAppointment appointmentAfter = null;
		Calendar time = Calendar.getInstance();
		time.setTimeInMillis(mobileSchedulerJob.getTime());
		time = resetSecAndMils(time);
		List<AbstractAppointment> abstractAppointments = downloadAppointmentForNearestBrief(mobileSchedulerJob);
		
		Calendar bt = Calendar.getInstance();
		for (AbstractAppointment abstractAppointment : abstractAppointments) {
			bt.setTimeInMillis(abstractAppointment.getBeginTime());
			bt = resetSecAndMils(bt); 
			if (bt.getTimeInMillis() <= time.getTimeInMillis()) {
				appointmentBefore = abstractAppointment;
			}
			else {
				break;
			}
		}
		for (AbstractAppointment abstractAppointment : abstractAppointments) {
			bt.setTimeInMillis(abstractAppointment.getBeginTime());
			bt = resetSecAndMils(bt); 
			if (bt.getTimeInMillis() <= time.getTimeInMillis()) {
			}
			else {
				appointmentAfter = abstractAppointment;
				break;
			}
		}
		
		long beforeDiff = -1;
		long  afterDiff = -1;
		if (appointmentBefore != null) {
			beforeDiff = time.getTimeInMillis() - appointmentBefore.getBt();
		}
		if (appointmentAfter != null) {
			afterDiff = appointmentAfter.getBt() - time.getTimeInMillis();
		}
		if ((beforeDiff != -1) && (afterDiff != -1)) {
			if (beforeDiff < afterDiff) {
				mobileAppointmentBrief = captureAppointmentBriefData(appointmentBefore);
			}
			else if (beforeDiff > afterDiff) {
				mobileAppointmentBrief = captureAppointmentBriefData(appointmentAfter);
			}
		}
		else if ((beforeDiff == -1) && (afterDiff > -1)) {
			mobileAppointmentBrief = captureAppointmentBriefData(appointmentAfter);
		}
		else if ((beforeDiff > -1) && (afterDiff == -1)) {
			mobileAppointmentBrief = captureAppointmentBriefData(appointmentBefore);
		}
		return mobileAppointmentBrief;
	}

}
